package net.jarang.support.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import net.jarang.support.PropertiesUtil;



public class DBUtil {
	private static final String RESOURCE_NAME = "jdbc.properties";
	
	public static Connection getConnection() {
		Connection conn = null;
		String url = getProperties().get("jdbc.url") + "";
		String driver = getProperties().get("jdbc.driver") + "";
		String id = getProperties().get("jdbc.username") + "";
		String pass = getProperties().get("jdbc.password") + "";

		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, id, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	public static void releaseConnection(Connection conn, PreparedStatement pstmt) {
		try {
			if (pstmt != null) { pstmt.close(); }
		} catch (Exception e) { }

		try {
			if (conn != null) { conn.close(); }
		} catch (Exception e) {	}
	}

	public static void releaseConnection(Connection conn, PreparedStatement pstmt, ResultSet rs) {
			try {
				if (rs != null) { rs.close(); }
			} catch (Exception e) { }

			try {
				if (pstmt != null) { pstmt.close(); }
			} catch (Exception e) { }

			try {
				if (conn != null) { conn.close(); }
			} catch (Exception e) { }
	}
	
	public static List<Column> getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) {
		Connection conn = null;
		DatabaseMetaData dbMetaData = null;
		ResultSet res = null;
		List<Column> columns = null;
		try {
			conn = DBUtil.getConnection();
			dbMetaData = conn.getMetaData();
			res = dbMetaData.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
			columns = new ArrayList<Column>();
			while (res.next()) {
				columns.add(new Column(
						res.getString("TABLE_SCHEM"), 
						res.getString("TABLE_NAME"), 
						res.getString("COLUMN_NAME"), 
						res.getInt("DATA_TYPE")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.releaseConnection(conn, null, res);
		}
		return columns;
	}
	
	public static List<PrimaryKey> getPrimaryKeys(String catalog, String schema, String table) {
		Connection conn = null;
		DatabaseMetaData dbMetaData = null;
		ResultSet res = null;
		List<PrimaryKey> primaryKeys = null;
		try {
			conn = DBUtil.getConnection();
			dbMetaData = conn.getMetaData();
			res = dbMetaData.getPrimaryKeys(catalog, schema, table);
			primaryKeys = new ArrayList<PrimaryKey>();
			while (res.next()) {
				primaryKeys.add(new PrimaryKey(
						res.getString("COLUMN_NAME"), 
						res.getString("KEY_SEQ"), 
						res.getString("PK_NAME")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.releaseConnection(conn, null, res);
		}
		return primaryKeys;
	}	

	private static Properties getProperties() {
		return PropertiesUtil.getProperties(RESOURCE_NAME);
	}
}
